USE [Weval test]
GO
/* This query has optional parameters that have not yet been implemented in the html
WHEN/if sorting and filtering is added to the html, data will need to be supplied to the cfm 
https://chitester1dev.weber.edu:6838/misc/weber/csevals/MyRanking.cfm
https://chitester1dev.weber.edu:6838/misc/weber/csevals/testtable.html
https://chitester1dev.weber.edu:6838/misc/weber/csevals/TestGraphZoom.html*/
/****** Object:  StoredProcedure [dbo].[CrnStatistics]    Script Date: 8/14/2013 8:16:47 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter PROCEDURE [dbo].[Ranking] @CRN int = null, @year int = null, @semester int = null, @questionID int = null
AS
BEGIN
	SET NOCOUNT ON;

DECLARE @depCode varchar(20) =
(	
	SELECT 
		case	WHEN code LIKE '2008' then '2' --these codes correspond to the deans of the college.  If it isn't the dean then we just want the code for the department chair
			WHEN code LIKE '3005' then '3'
			WHEN code LIKE '4004' then '4'
			WHEN code LIKE '5003' then '5'
			WHEN code LIKE '6007' then '6'
			WHEN code LIKE '7011' then '7'
			WHEN code LIKE '8005' then '8'
			WHEN code LIKE '0056' then '10'
			WHEN code LIKE '0026' then 'CE'
			ELSE code END
	FROM
		Users U
		JOIN UserRoles UR ON UR.userID=U.ID
		JOIN Roles R ON R.ID=UR.roleID
		LEFT JOIN InstructorDepartments ID ON ID.instructorID=U.ID
		LEFT JOIN Departments D ON D.code=ID.departmentCode
	WHERE R.name IN ('Dean','Department Chair','Administrator')
		AND (UR.revoked IS NULL or UR.revoked = 1900-1-1)
		AND U.id = @userID
);

WITH canView as 
(
		SELECT U.Id, ST.sectionID
		,MIN(CASE
			WHEN U.Id = @userID then 1
			WHEN RL.name = 'Tenure' and (se.sharewithchairsdeans is null or se.sharewithchairsdeans != 'true') then 0
			WHEN @depCode = 10 then 1
			WHEN Dp.code = @depCode THEN 1
			WHEN DP.collegeCode = @depCode THEN 1
			ELSE 0 END) as canView
			
		FROM SectionsTaught ST
			INNER JOIN Users U ON ST.instructorID = U.ID
			INNER JOIN UserRoles UR ON UR.userID = U.ID
			INNER JOIN Roles Rl ON Rl.ID = UR.roleID
			LEFT JOIN InstructorDepartments ID ON ID.instructorID = U.ID
			LEFT JOIN Departments Dp ON Dp.code = ID.departmentCode
			LEFT JOIN ShareEvals se ON U.Id = se.instructorId

		GROUP BY U.Id, ST.sectionID
),
cteMain as
(
	SELECT 
		CASE 
			WHEN cv.canView != 0 THEN  U.FirstName + ' ' + U.LastName 
			ELSE '-'
		END AS iName,
		CASE
			WHEN U.ID = @userID THEN 'red'
			WHEN cv.canView != 0 then 'green'
			ELSE 'blue'
		END AS marker,
		CASE 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly disagree' THEN 0.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'very poor' THEN 0.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0 WHEN LOWER(CAST(D.text AS nvarchar(255)))= 'poor' THEN 1.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 2.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'average' THEN 2.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 3.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'good' THEN 3.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 4.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'excellent' THEN 4.0
		END Score,
			QR.sequence,
			bannerCRN,
			year,
			semester,
			CS.subjectCode,
			CS.courseNumber,
			ST.instructorID,
			cs.id as CSID,
			studentID
		FROM SectionsTaught ST
			INNER JOIN Results R ON R.sectionID = ST.sectionID
				AND (R.deleted = 0 OR R.deleted IS NULL)
			INNER JOIN CourseSections CS ON CS.ID = R.sectionID
				AND CS.year = coalesce(@year, CS.year) --if no year is supplied then get all years
				AND CS.semester = coalesce(@semester, CS.semester)
				--AND CS.subjectcode = 'CS'
			INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
			INNER JOIN Questions Q ON Q.ID = QR.questionID
				--AND Q.ID = coalesce(@questionID, Q.ID)
			INNER JOIN Tests T ON T.ID = Q.testID 
				AND T.typeID = 5 --Course Eval
			INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
			INNER JOIN Distractors D ON D.ID = DR.distractorID
			INNER JOIN Users U ON st.instructorID = u.Id
			INNER JOIN InstructorDepartments ID ON ID.instructorID = U.ID	
				AND (ID.departmentCode LIKE '8%')
			LEFT JOIN Departments Dp ON Dp.code = ID.departmentCode
			LEFT JOIN canView cv ON st.instructorID = cv.Id and cv.sectionId = CS.Id
),
cteAverage AS
(
	SELECT CSID,
		marker,
		year,
		semester,
		subjectCode + ' ' + courseNumber as course,
		bannerCRN,
		iName,
		AVG(score) AS InstructorAverage,
		count(DISTINCT studentID) as StudentCount
		FROM 
		cteMain
	GROUP BY
		CSID, marker, year, semester, courseNumber, subjectCode, bannerCRN, iName
)
SELECT *,
	NTILE(10) OVER(ORDER BY InstructorAverage ASC) AS DecTile
FROM cteAverage A
where instructoraverage is not null and studentcount > 5
ORDER BY instructoraverage desc
END
GO
